# -*- coding: utf-8 -*-

# pip install pymysql
# apt-get install python3-pymysql

import sys
import pymysql as pymysql
from loguru import logger


class EasyMysql(object):

    def __init__(self, DB_Host=None, DB_Port=None, DB_User=None, DB_Passwd=None, DB_DataBase=None, DB_Charset='utf8'):
        super(EasyMysql, self).__init__()
        # from DBUtils.PooledDB import PooledDB
        self.DB_Host = DB_Host
        self.DB_Port = DB_Port
        self.DB_User = DB_User
        self.DB_Passwd = DB_Passwd
        self.DB_DataBase = DB_DataBase
        self.DB_Charset = DB_Charset
        # self.DBPool = None
        # if self.DB_Host is not None:
        #    self.DBPool = PooledDB(pymysql, mincached=1, maxcached=2, host=self.DB_Host, port=self.DB_Port, user=self.DB_User, passwd=self.DB_Passwd, db=self.DB_DataBase, charset=self.DB_Charset)  # ,setsession=['SET AUTOCOMMIT = 1']

    def __del__(self):
        #
        pass

    def Mysql_Select(self, sql, values):
        __functionname__ = sys._getframe().f_code.co_name
        err = True
        message = ""
        data = []

        try:
            # db = self.DBPool.connection()
            db = pymysql.connect(host=self.DB_Host, port=self.DB_Port, user=self.DB_User,
                                 passwd=self.DB_Passwd, db=self.DB_DataBase, charset=self.DB_Charset)
            cursor = db.cursor()
            logger.debug("mysql_select SQL= {0}".format(sql))
            logger.debug("mysql_select VALUES = {0}".format(values))
            cursor.execute(sql, values)
            data = list(cursor.fetchall())

        except Exception as e:
            logger.error("[{0}] = {1}".format(__functionname__, e))
            err = False
            message = "{0}".format(e)
        finally:
            cursor.close()
            db.close()

        return err, message, data

    def Mysql_Insert(self, table, values):
        __functionname__ = sys._getframe().f_code.co_name
        err = True
        message = ""

        try:
            # db = self.DBPool.connection()
            db = pymysql.connect(host=self.DB_Host, port=self.DB_Port, user=self.DB_User,
                                 passwd=self.DB_Passwd, db=self.DB_DataBase, charset=self.DB_Charset)
            cursor = db.cursor()
            SQLStr = "insert into `%s` values %%s" % (table)
            logger.debug("{0} = {1}".format(__functionname__, SQLStr))
            logger.debug("{0} = {1}".format(__functionname__, values))
            message = cursor.execute(SQLStr, [values])
            db.commit()
            logger.info("[{0}] = {1}".format(__functionname__, values))
        except Exception as e:
            db.rollback()
            logger.error("[{0}] = {1}".format(__functionname__, e))
            err = False
            message = "{0}".format(e)
        finally:
            cursor.close()
            db.close()

        return err, message

    def Mysql_Insert_Ex(self, table, fields, values):
        __functionname__ = sys._getframe().f_code.co_name
        err = True
        message = ""

        try:
            # db = self.DBPool.connection()
            db = pymysql.connect(host=self.DB_Host, port=self.DB_Port, user=self.DB_User,
                                 passwd=self.DB_Passwd, db=self.DB_DataBase, charset=self.DB_Charset)
            cursor = db.cursor()
            SQLStr = "insert into `%s` %s values %%s" % (table, tuple(fields))
            SQLStr = SQLStr.replace("'", "`")
            logger.debug("{0} = {1}".format(__functionname__, SQLStr))
            logger.debug("{0} = {1}".format(__functionname__, values))
            message = cursor.execute(SQLStr, [values])
            db.commit()
            logger.debug("[{0}] = {1}".format(__functionname__, values))
        except Exception as e:
            db.rollback()
            logger.error("[{0}] = {1}".format(__functionname__, e))
            err = False
            message = "{0}".format(e)
        finally:
            cursor.close()
            db.close()

        return err, message

    def Mysql_Update(self, sql, values):
        __functionname__ = sys._getframe().f_code.co_name
        err = True
        message = ""

        try:
            # db = self.DBPool.connection()
            db = pymysql.connect(host=self.DB_Host, port=self.DB_Port, user=self.DB_User,
                                 passwd=self.DB_Passwd, db=self.DB_DataBase, charset=self.DB_Charset)
            cursor = db.cursor()
            logger.debug("{0} = {1}".format(__functionname__, sql))
            logger.debug("{0} = {1}".format(__functionname__, values))

            message = cursor.execute(sql, values)
            db.commit()
            logger.debug("[{0}] = {1} => {2}".format(
                __functionname__, sql, values))
        except pymysql.Error as e:
            logger.error("[{0}] = {1}".format(__functionname__, e))
            err = False
            message = "{0}".format(e)

        except Exception as e:
            db.rollback()
            logger.error("[{0}] = {1}".format(__functionname__, e))
            err = False
            message = "{0}".format(e)
        finally:
            cursor.close()
            db.close()

        return err, message

    def Mysql_Execute(self, sql, values):
        __functionname__ = sys._getframe().f_code.co_name
        err = True
        message = ""

        try:
            # db = self.DBPool.connection()
            db = pymysql.connect(host=self.DB_Host, port=self.DB_Port, user=self.DB_User,
                                 passwd=self.DB_Passwd, db=self.DB_DataBase, charset=self.DB_Charset)
            cursor = db.cursor()
            logger.debug("sql_execute SQL = {0}".format(sql))
            logger.debug("sql_execute VALUES = {0}".format(values))
            message = cursor.execute(sql, values)
            db.commit()
            logger.debug("[{0}] = {1} => {2}".format(
                __functionname__, sql, values))
        except Exception as e:
            db.rollback()
            logger.error("[{0}] = {1}".format(__functionname__, e))
            err = False
            message = "{0}".format(e)
        finally:
            cursor.close()
            db.close()

        return err, message
